﻿//=======================================================================================
// Microsoft Windows Server AppFabric Customer Advisory Team (CAT) Best Practices Series
//
// This sample is supplemental to the technical guidance published on the community
// blog at http://blogs.msdn.com/appfabriccat/. 
//
//=======================================================================================
// Copyright © 2010 Microsoft Corporation. All rights reserved.
// 
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER 
// EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF 
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. YOU BEAR THE RISK OF USING IT.
//=======================================================================================

using System.Xml;
using System.Data;
using System.Data.SqlClient;

namespace HashFoo.SqlServer.TransientDbConnection
{
    #region Using references
    
    #endregion

    /// <summary>
    /// Provides a set of extension methods adding retry capabilities into the standard System.Data.SqlClient.SqlCommand implementation.
    /// </summary>
    public static class SqlCommandExtensions
    {
        #region ExecuteNonQueryWithRetry method implementations
        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns the number of rows affected. Uses the default retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQueryWithRetry(this SqlCommand command)
        {
            return ExecuteNonQueryWithRetry(command, RetryPolicyFactory.GetDefaultSqlCommandRetryPolicy());
        }

        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns the number of rows affected. Uses the specified retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQueryWithRetry(this SqlCommand command, RetryPolicy retryPolicy)
        {
            return ExecuteNonQueryWithRetry(command, retryPolicy, RetryPolicy.NoRetry);
        }

        /// <summary>
        /// Executes a Transact-SQL statement against the connection and returns the number of rows affected. Uses the specified retry policy when executing the command.
        /// Uses a separate specified retry policy when establishing a connection.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="cmdRetryPolicy">The command retry policy defining whether to retry a command if it fails while executing.</param>
        /// <param name="conRetryPolicy">The connection retry policy defining whether to re-establish a connection if it drops while executing the command.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteNonQueryWithRetry(this SqlCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
        {
            // Check if retry policy was specified, if not, use the default retry policy.
            return (cmdRetryPolicy != null ? cmdRetryPolicy : RetryPolicy.NoRetry).ExecuteAction<int>(() =>
            {
                EnsureValidConnection(command, conRetryPolicy);
                return command.ExecuteNonQuery();
            });
        } 
        #endregion

        #region ExecuteReaderWithRetry method implementations
        /// <summary>
        /// Sends the specified command to the connection and builds a SqlDataReader object containing the results.
        /// Uses the default retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
        public static SqlDataReader ExecuteReaderWithRetry(this SqlCommand command)
        {
            return ExecuteReaderWithRetry(command, RetryPolicyFactory.GetDefaultSqlCommandRetryPolicy());
        }

        /// <summary>
        /// Sends the specified command to the connection and builds a SqlDataReader object containing the results.
        /// Uses the specified retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param>
        /// <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
        public static SqlDataReader ExecuteReaderWithRetry(this SqlCommand command, RetryPolicy retryPolicy)
        {
            return ExecuteReaderWithRetry(command, retryPolicy, RetryPolicy.NoRetry);
        }

        /// <summary>
        /// Sends the specified command to the connection and builds a SqlDataReader object containing the results.
        /// Uses the specified retry policy when executing the command. Uses a separate specified retry policy when
        /// establishing a connection.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="cmdRetryPolicy">The command retry policy defining whether to retry a command if it fails while executing.</param>
        /// <param name="conRetryPolicy">The connection retry policy defining whether to re-establish a connection if it drops while executing the command.</param>
        /// <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
        public static SqlDataReader ExecuteReaderWithRetry(this SqlCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
        {
            // Check if retry policy was specified, if not, use the default retry policy.
            return (cmdRetryPolicy != null ? cmdRetryPolicy : RetryPolicy.NoRetry).ExecuteAction<SqlDataReader>(() =>
            {
                EnsureValidConnection(command, conRetryPolicy);
                return command.ExecuteReader();
            });
        }

        /// <summary>
        /// Sends the specified command to the connection and builds a SqlDataReader object using one of the 
        /// CommandBehavior values. Uses the default retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="behavior">One of the System.Data.CommandBehavior values.</param>
        /// <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
        public static SqlDataReader ExecuteReaderWithRetry(this SqlCommand command, CommandBehavior behavior)
        {
            return ExecuteReaderWithRetry(command, behavior, RetryPolicyFactory.GetDefaultSqlCommandRetryPolicy());
        }

        /// <summary>
        /// Sends the specified command to the connection and builds a SqlDataReader object using one of the
        /// CommandBehavior values. Uses the specified retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="behavior">One of the System.Data.CommandBehavior values.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param>
        /// <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
        public static SqlDataReader ExecuteReaderWithRetry(this SqlCommand command, CommandBehavior behavior, RetryPolicy retryPolicy)
        {
            return ExecuteReaderWithRetry(command, behavior, retryPolicy, RetryPolicy.NoRetry);
        }

        /// <summary>
        /// Sends the specified command to the connection and builds a SqlDataReader object using one of the
        /// CommandBehavior values. Uses the specified retry policy when executing the command.
        /// Uses a separate specified retry policy when establishing a connection.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="behavior">One of the System.Data.CommandBehavior values.</param>
        /// <param name="cmdRetryPolicy">The command retry policy defining whether to retry a command if it fails while executing.</param>
        /// <param name="conRetryPolicy">The connection retry policy defining whether to re-establish a connection if it drops while executing the command.</param>
        /// <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
        public static SqlDataReader ExecuteReaderWithRetry(this SqlCommand command, CommandBehavior behavior, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
        {
            // Check if retry policy was specified, if not, use the default retry policy.
            return (cmdRetryPolicy != null ? cmdRetryPolicy : RetryPolicy.NoRetry).ExecuteAction<SqlDataReader>(() =>
            {
                EnsureValidConnection(command, conRetryPolicy);
                return command.ExecuteReader(behavior);
            });
        } 
        #endregion

        #region ExecuteScalarWithRetry method implementations
        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
        /// Uses the default retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <returns> The first column of the first row in the result set, or a null reference if the result set is empty. Returns a maximum of 2033 characters.</returns>
        public static object ExecuteScalarWithRetry(this SqlCommand command)
        {
            return ExecuteScalarWithRetry(command, RetryPolicyFactory.GetDefaultSqlCommandRetryPolicy());
        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
        /// Uses the specified retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param>
        /// <returns> The first column of the first row in the result set, or a null reference if the result set is empty. Returns a maximum of 2033 characters.</returns>
        public static object ExecuteScalarWithRetry(this SqlCommand command, RetryPolicy retryPolicy)
        {
            return ExecuteScalarWithRetry(command, retryPolicy, RetryPolicy.NoRetry);
        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
        /// Uses the specified retry policy when executing the command. Uses a separate specified retry policy when establishing a connection.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="cmdRetryPolicy">The command retry policy defining whether to retry a command if it fails while executing.</param>
        /// <param name="conRetryPolicy">The connection retry policy defining whether to re-establish a connection if it drops while executing the command.</param>
        /// <returns> The first column of the first row in the result set, or a null reference if the result set is empty. Returns a maximum of 2033 characters.</returns>
        public static object ExecuteScalarWithRetry(this SqlCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
        {
            // Check if retry policy was specified, if not, use the default retry policy.
            return (cmdRetryPolicy != null ? cmdRetryPolicy : RetryPolicy.NoRetry).ExecuteAction<object>(() =>
            {
                EnsureValidConnection(command, conRetryPolicy);
                return command.ExecuteScalar();
            });
        }
        #endregion

        #region ExecuteXmlReaderWithRetry method implementations
        /// <summary>
        /// Sends the specified command to the connection and builds an XmlReader object containing the results.
        /// Uses the default retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <returns>An System.Xml.XmlReader object.</returns>
        public static XmlReader ExecuteXmlReaderWithRetry(this SqlCommand command)
        {
            return ExecuteXmlReaderWithRetry(command, RetryPolicyFactory.GetDefaultSqlCommandRetryPolicy());
        }

        /// <summary>
        /// Sends the specified command to the connection and builds an XmlReader object containing the results.
        /// Uses the specified retry policy when executing the command.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param>
        /// <returns>An System.Xml.XmlReader object.</returns>
        public static XmlReader ExecuteXmlReaderWithRetry(this SqlCommand command, RetryPolicy retryPolicy)
        {
            return ExecuteXmlReaderWithRetry(command, retryPolicy, RetryPolicy.NoRetry);
        }

        /// <summary>
        /// Sends the specified command to the connection and builds an XmlReader object containing the results.
        /// Uses the specified retry policy when executing the command. Uses a separate specified retry policy when establishing a connection.
        /// </summary>
        /// <param name="command">The command object which is required as per extension method declaration.</param>
        /// <param name="cmdRetryPolicy">The command retry policy defining whether to retry a command if it fails while executing.</param>
        /// <param name="conRetryPolicy">The connection retry policy defining whether to re-establish a connection if it drops while executing the command.</param>
        /// <returns>An System.Xml.XmlReader object.</returns>
        public static XmlReader ExecuteXmlReaderWithRetry(this SqlCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
        {
            // Check if retry policy was specified, if not, use the default retry policy.
            return (cmdRetryPolicy != null ? cmdRetryPolicy : RetryPolicy.NoRetry).ExecuteAction<XmlReader>(() =>
            {
                EnsureValidConnection(command, conRetryPolicy);
                return command.ExecuteXmlReader();
            });
        }
        #endregion

        #region Private methods
        private static void EnsureValidConnection(SqlCommand command, RetryPolicy retryPolicy)
        {
            if (command != null)
            {
                // Verify whether or not the connection is valid and is open. This code may be retried therefore
                // it is important to ensure that a connection is re-established should it have previously failed.
                if (!(command.Connection != null && command.Connection.State == ConnectionState.Open))
                {
                    // Attempt to open the connection using the retry policy that matches the policy for SQL commands.
                    command.Connection.OpenWithRetry(retryPolicy);
                }
            }
        }
        #endregion
    }
}
